package com.scalar.chiptrack.operations.finishedGoods.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;

import org.apache.struts.util.LabelValueBean;

import com.scalar.ScalarException;
import com.scalar.chiptrack.commons.Constants;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.operations.finishedGoods.EquivalentPartNumber;
import com.scalar.chiptrack.operations.finishedGoods.FinishedGoodsPartNumber;
import com.scalar.chiptrack.operations.finishedGoods.forms.FinishedGoodsEntryForm;
import com.scalar.chiptrack.operations.finishedGoods.forms.FinishedGoodsSearchForm;
import com.scalar.chiptrack.operations.process.VendorLocation;
import com.scalar.chiptrack.operations.processtypesetup.ProcessParameters;
import com.scalar.chiptrack.utils.SqlQueryMappings;

/**
 * User: Murali Krishna Yengala
 * Date: May 26, 2005
 * Time: 1:46:55 PM
 */
public class FinishedGoodsDAO {

    /**
     *
     * @return
     * @throws ScalarException
     */
    public static ArrayList getBaseProducts() throws ScalarException
    {
        Connection con = null;
        Statement stmt = null;
        ResultSet rs   = null;
        ArrayList baseProductsList = new ArrayList();

        try
        {
            con = DataSource.getConnection();
            stmt = con.createStatement();
            rs = stmt.executeQuery(SqlQueryMappings.get("operations.process.product.selectBaseProduct"));
            while( rs.next() )
            {
                String productId = rs.getString("PRODUCT_ID");
                String partNumber = rs.getString("PART_NUMBER");
                baseProductsList.add( new LabelValueBean(partNumber, productId));
            }
        }
        catch( Exception e )
        {
            throw new ScalarException("Exception while fetching Base Parts !");
        }
        finally
        {
            if (rs != null) {
                try { rs.close(); } catch (SQLException ignore) {}
            }
            if (stmt != null) {
                try { stmt.close(); } catch (SQLException ignore) {}
            }
            if (con != null) {
                try { con.close(); } catch (SQLException ignore) {}
            }
        }
        return baseProductsList;
    }

    public static ArrayList getFgiProducts(int baseProductId) throws ScalarException
    {
        Connection con = null;
        PreparedStatement stmt = null;
        ResultSet rs   = null;
        ArrayList fgiPartsList = new ArrayList();

        try
        {
            con = DataSource.getConnection();
            stmt = con.prepareStatement(SqlQueryMappings.get("operations.finishedGoods.getFgiProducts"));
            //stmt = con.prepareStatement("SELECT PRODUCT_ID,PART_NUMBER FROM PRODUCT A,PROCESS_STEP B WHERE P_PARENT_PRODUCT_ID = ? AND A.PROCESSSTEP_ID = B.PROCESSSTEP_ID AND B.PS_TYPE LIKE 'T%' AND B.PS_TYPE_NAME LIKE 'Class Store' ORDER BY PART_NUMBER");
            stmt.setInt(1, baseProductId );
            rs = stmt.executeQuery();
            while( rs.next())
            {
                String processStepId = rs.getString("PROCESSSTEP_ID");
                String partNumber = rs.getString("PART_NUMBER");
                fgiPartsList.add( new LabelValueBean(partNumber, processStepId));
            }
        }
        catch( Exception e)
        {
            throw new ScalarException("Exception while fetching Fgi parts !");
        }
        finally
        {
            if (rs != null) {
                try { rs.close(); } catch (SQLException ignore) {}
            }
            if (stmt != null) {
                try { stmt.close(); } catch (SQLException ignore) {}
            }
            if (con != null) {
                try { con.close(); } catch (SQLException ignore) {}
            }
        }
        return fgiPartsList;
    }

    public static ArrayList getAllCustomers() throws ScalarException
    {
        Connection con = null;
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList customersList = new ArrayList();

        try
        {
            con = DataSource.getConnection();
            stmt = con.createStatement();
            rs = stmt.executeQuery(SqlQueryMappings.get("sales.salesOrderInvoice.loadAllCustomers"));
            while( rs.next())
            {
                String customerId = rs.getString("CUSTOMER_ID");
                String customerName = rs.getString("C_NAME");
                customersList.add( new LabelValueBean( customerName, customerId));
            }
        }
        catch( Exception e)
        {
            throw new ScalarException("Exception while fetching Customers List !");
        }
        finally
        {
            if (rs != null) {
                try { rs.close(); } catch (SQLException ignore) {}
            }
            if (stmt != null) {
                try { stmt.close(); } catch (SQLException ignore) {}
            }
            if (con != null) {
                try { con.close(); } catch (SQLException ignore) {}
            }
        }
        return customersList;
    }

    public static void saveParameters(DBTransaction transaction, int processStepId, ArrayList parameters ) throws ScalarException{
		Connection conn = null;
		PreparedStatement ps = null;

		try
		{
			if(transaction == null){
				conn = DataSource.getConnection();
			}else{
				conn = transaction.getConnection();
			}
			ps = conn.prepareStatement( SqlQueryMappings.get("operations.process.deleteProcessParameters") );
			ps.setInt( 1 , processStepId );
			ps.executeUpdate();
			ps.close();
			ps = null;
			if( parameters != null && parameters.size() > 0  )
			{
                ps = conn.prepareStatement(SqlQueryMappings.get("operations.finishedGoods.insertProcessParameters"));
                Iterator parameterIte = parameters.iterator();
                while( parameterIte.hasNext() )
                {
                    ProcessParameters parameter = (ProcessParameters) parameterIte.next();
                    ps.setInt(1, processStepId);
                    ps.setString(2, parameter.getParamName());
                    ps.setString(3, parameter.getParamUnit());
                    ps.setString(4, parameter.getParamVal());
                    ps.addBatch();
                }
				ps.executeBatch();
			}
		}catch(Exception e){
			e.printStackTrace();
			throw new ScalarException("Could not save the Process Parameters data ", e);
		}finally{
			if (transaction == null){
				try {
					if (conn != null){
						conn.commit();
					}
				}catch (SQLException ignore) {}
			}
			if(ps != null){
				try{ ps.close(); }catch(Exception e){e.printStackTrace();}
			}
			if(conn != null){
				try{ conn.close(); }catch(Exception e){e.printStackTrace();}
			}
		}
	}

    public static void saveProductMap(DBTransaction dbTransaction,int processStepId, ArrayList dupPartNumbers, ArrayList vendorLocationList)throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean status = false;
        VendorLocation vendorLocation = null;
        try
        {
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement(SqlQueryMappings.get("operations.process.deleteFromProductMap"));
            pstmt.setInt(1,processStepId);
            pstmt.executeUpdate();
            pstmt.close();
            pstmt = null;

            pstmt = conn.prepareStatement(SqlQueryMappings.get("operations.process.insertProductMap"));

            for (int y=0 ; y < vendorLocationList.size() ; y++)
            {
                if( dupPartNumbers != null)
                {
                    Iterator dupPartIte = dupPartNumbers.iterator();
                    while( dupPartIte.hasNext())
                    {
                        EquivalentPartNumber dupPart = (EquivalentPartNumber) dupPartIte.next();
                        String partNo = dupPart.getPartNo();
                        partNo = (partNo == null ? "" : partNo.trim());
                        if( !"".equals( partNo))
                        {
                            vendorLocation = (VendorLocation)vendorLocationList.get(y);
                            pstmt.setInt(1,processStepId);
                            pstmt.setString(2,partNo);
                            pstmt.setInt(3,vendorLocation.getLocationIDAsInt());
                            pstmt.addBatch();
                        }

                    }
                }
            }
            pstmt.executeBatch();
            pstmt.close();
            pstmt=null;
            status = true;
        }
        catch(SQLException sql)
        {
            sql.printStackTrace();
            throw new ScalarException(" Save saveProductMap Failed", sql);
        }
        finally
        {
        // Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbTransaction == null) {
                try {
                    if (conn != null) {
                        if (status == true) {
                            conn.commit();
                        }
                        else {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore) {}
            }

            if(pstmt != null){
                try{ pstmt.close(); }catch(Exception e){}
            }
            if(conn != null){
                try{ conn.close(); }catch(Exception e){}
            }
        }
    }

    public static void loadProductInfoByProcessStepId( int processStepId, FinishedGoodsEntryForm form ) throws ScalarException
    {
        Connection conn 		= null;
		PreparedStatement pstmt = null;
		ResultSet rs 			= null;
		try
		{
			conn = DataSource.getConnection();
			pstmt = conn.prepareStatement( SqlQueryMappings.get("operations.finishedGoods.getProductInfo" ));
            pstmt.setInt( 1, processStepId );
            pstmt.setInt( 2, Integer.parseInt( form.getCustomerId() ) );
			rs = pstmt.executeQuery();
			if( rs.next() )
			{
                form.setFinishedGoodsProductId( rs.getInt("Product_ID"));
                form.setFinishedGoodsPartNo( rs.getString("Part_Number"));
                form.setBaseProductId( rs.getString("P_Parent_Product_ID"));
                form.setDescription( rs.getString("P_Comments"));
                form.setApproved( ( rs.getInt("Is_Approved")==0 ? true : false) );
                form.setCustomerId( rs.getString("Customer_ID"));
                form.setProgramCode( rs.getString("P_ProgramCode") );
                form.setAlgorithm( rs.getString("P_Algorithm") );
                form.setCrc( rs.getString( "P_CRC_Code"));
                form.setIsBlankProduct( rs.getString ( "Is_Blank_Product" ) );
            }
		}
		catch( Exception e )
		{
            e.printStackTrace();
			throw new ScalarException ("Could not get the part information from the database for process step ID: " + processStepId );
		}
		finally
		{
			if (rs != null)
			{
				try { rs.close(); } catch (SQLException ignore) {}
            }

			if (pstmt != null)
			{
				try { pstmt.close(); } catch (SQLException ignore) {}
            }

			if (conn != null)
			{
				try { conn.close(); } catch (SQLException ignore) {}
			}
		}
    }

    public static ArrayList loadProcessParametersByProcessStepId( int processStepId  ) throws ScalarException
    {
        Connection conn 		= null;
		PreparedStatement pstmt = null;
		ResultSet rs 			= null;
        ArrayList processParameters = new ArrayList();
		try
		{
			conn = DataSource.getConnection();
			pstmt = conn.prepareStatement( SqlQueryMappings.get("operations.finishedGoods.getProcessParameters" ));
            pstmt.setInt( 1, processStepId );
			rs = pstmt.executeQuery();

			while( rs.next() )
			{
                ProcessParameters parameter = new ProcessParameters();
                parameter.setParamName( rs.getString("Parameter_Key"));
                parameter.setParamUnit( rs.getString("Parameter_Units"));
                parameter.setParamVal( rs.getString("Parameter_Value"));
                processParameters.add( parameter);
			}
		}
		catch( Exception e )
		{
			throw new ScalarException ("Could not get the process parameter information from the database for process step ID: " + processStepId );
		}
		finally
		{
			if (rs != null)
			{
				try { rs.close(); } catch (SQLException ignore) {}
            }

			if (pstmt != null)
			{
				try { pstmt.close(); } catch (SQLException ignore) {}
            }

			if (conn != null)
			{
				try { conn.close(); } catch (SQLException ignore) {}
			}
		}
        return processParameters;
    }

    public static void saveFgToFgiMappings( DBTransaction dbTransaction, int processStepId, String[] fgiProcessStepIds, int baseProductId) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean status = false;
        try
        {
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement(SqlQueryMappings.get("operations.finishedGoods.insertFgtoFgiMappings"));
            for(int i = 0;i < fgiProcessStepIds.length; i++ )
            {
                int processStep_id = -1;
                try
                {
                    processStep_id = Integer.parseInt( fgiProcessStepIds[i] );
                }
                catch( NumberFormatException e)
                {
                    continue;
                }
                pstmt.setInt(1, processStepId);
                pstmt.setInt( 2, processStep_id);
                pstmt.setInt(3, baseProductId);
                pstmt.addBatch();
            }
            pstmt.executeBatch();

            if ( pstmt != null )
            {
                pstmt.close();
                pstmt = null;
            }

            pstmt = conn.prepareStatement(SqlQueryMappings.get("operations.process.product.updateNextProcessStepId"));
            for(int i = 0;i < fgiProcessStepIds.length; i++ )
            {
                int processStep_id = -1;
                try
                {
                    processStep_id = Integer.parseInt( fgiProcessStepIds[i] );
                }
                catch( NumberFormatException e)
                {
                    continue;
                }
                pstmt.setInt(1, processStep_id);
                pstmt.setInt( 2, processStepId);
                pstmt.setInt(3, baseProductId);
                pstmt.addBatch();
            }
            pstmt.executeBatch();
        }
        catch(SQLException sql)
        {
            sql.printStackTrace();
            throw new ScalarException(" Save saveProductMap Failed", sql);
        }
        finally
        {
        // Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbTransaction == null) {
                try {
                    if (conn != null) {
                        if (status == true) {
                            conn.commit();
                        }
                        else {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore) {}
            }

            if(pstmt != null){
                try{ pstmt.close(); }catch(Exception e){}
            }
            if(conn != null){
                try{ conn.close(); }catch(Exception e){}
            }
        }
    }

    public static void deleteFromFgToFgiMapTable( DBTransaction dbTransaction, int processStepId ) throws ScalarException
    {
        Connection con = null;
        PreparedStatement pStmt = null;
        boolean status = false;
        try
        {
            if( dbTransaction != null)
            {
                con = dbTransaction.getConnection();
            }
            else
            {
                con = DataSource.getConnection();
            }
            pStmt = con.prepareStatement( SqlQueryMappings.get("operations.finishedGoods.deleteFgtoFgiMappings"));
            pStmt.setInt(1, processStepId);
            pStmt.executeUpdate();

            pStmt = con.prepareStatement("DELETE FROM Process_Step_Traverse_Table WHERE NEXT_PROCESSSTEP_ID = ?");
            pStmt.setInt(1, processStepId);
            pStmt.executeUpdate();

            status = true;
        }
        catch( Exception e)
        {
            e.printStackTrace();
            throw new ScalarException("Couldn't update Product table");
        }
        finally
        {
            if (dbTransaction == null) {
                try {
                    if (con != null) {
                        if (status == true) {
                            con.commit();
                        }
                        else {
                            con.rollback();
                        }
                    }
                }
                catch (SQLException ignore) {}
            }

            if(pStmt != null){
                try{ pStmt.close(); }catch(Exception e){}
            }
            if(con != null){
                try{ con.close(); }catch(Exception e){}
            }
        }
    }



    public static String[] loadFgiProcessStepIdsByFgProcessStepId( int processStepId ) throws ScalarException
    {
        Connection conn 		= null;
        CallableStatement cstmt = null;
        ResultSet rs 			= null;
        String[] fgiProcessStepIds ;
        try
        {
            conn = DataSource.getConnection();
            cstmt = conn.prepareCall(SqlQueryMappings.get("operations.finishedGoods.getFgiProcessStepIds"));
            cstmt.setInt( 1, processStepId );
            rs = cstmt.executeQuery();
            ArrayList productIdsList = new ArrayList();
            while( rs.next() )
            {
                productIdsList.add(rs.getString(1));
            }
            fgiProcessStepIds = new String[ productIdsList.size()];
            Iterator fgiProductListIter = productIdsList.iterator();
            int idx = 0;
            while( fgiProductListIter.hasNext() )
            {
                fgiProcessStepIds[idx] = (String)fgiProductListIter.next();
                idx++;
            }
        }
        catch( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException ("Could not get the product ids for FGI products for process step id : " + processStepId );
        }
        finally
        {
            if (rs != null)
            {
                try { rs.close(); } catch (SQLException ignore) {}
            }

            if (cstmt != null)
            {
                try { cstmt.close(); } catch (SQLException ignore) {}
            }

            if (conn != null)
            {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }
        return fgiProcessStepIds;
    }

    /**
     *
     * @param programCode
     * @param customerIds
     * @param finishedGoodsProcessStepId == -1 INCASE IT IS A NEW FG
     * @param fgiProcessStepIds
     * @return
     * @throws ScalarException
     */

    public static ArrayList checkForProgramCodeDuplicates( String programCode, String[] customerIds, int finishedGoodsProcessStepId, String[] fgiProcessStepIds ) throws ScalarException
    {
        Connection con = null;
        CallableStatement pStmt = null;
        ResultSet rs = null;
        ArrayList fgiPartsAndCustomers = null;
        StringBuffer customerIdsBuffer = new StringBuffer();
        if( customerIds != null )
        {
            for (int i = 0; i < customerIds.length; i++)
            {
                String customerId = customerIds[i];
                if( customerId != null )
                {
                    customerId = customerId.trim();
                }
                customerIdsBuffer = customerIdsBuffer.append(customerId);
                if( i+1 != customerIds.length )
                {
                    customerIdsBuffer = customerIdsBuffer.append(';');
                }
            }
        }
        StringBuffer fgiProcessStepIdsBuffer = new StringBuffer();
        if( fgiProcessStepIds != null )
        {
            for (int i = 0; i < fgiProcessStepIds.length; i++)
            {
                String fgiProcessStepId = fgiProcessStepIds[i];
                if( fgiProcessStepId != null )
                {
                    fgiProcessStepId = fgiProcessStepId.trim();
                }
                fgiProcessStepIdsBuffer = fgiProcessStepIdsBuffer.append(fgiProcessStepId);
                if( i+1 != fgiProcessStepIds.length )
                {
                    fgiProcessStepIdsBuffer = fgiProcessStepIdsBuffer.append(';');
                }
            }
        }
        try
        {
            con = DataSource.getConnection();
            pStmt = con.prepareCall( SqlQueryMappings.get("operations.finishedGoods.getDupProgramCodes") );
            pStmt.setInt(1, finishedGoodsProcessStepId);
            pStmt.setString(2, customerIdsBuffer.toString());
            pStmt.setString(3, fgiProcessStepIdsBuffer.toString());
            pStmt.setString(4, programCode);
            rs = pStmt.executeQuery();
            fgiPartsAndCustomers = new ArrayList();
            while( rs.next())
            {
                String fgiPart = rs.getString( 1);
                String customer = rs.getString( 2);
                fgiPartsAndCustomers.add(fgiPart+", "+customer);
            }
        }
        catch( Exception e)
        {
            e.printStackTrace();
            throw new ScalarException( "Couldn't check for duplicate Program Codes in database");
        }
        finally
        {
            if (rs != null)
            {
                try { rs.close(); } catch (SQLException ignore) {}
            }
            if (pStmt != null)
            {
                try { pStmt.close(); } catch (SQLException ignore) {}
            }
            if (con != null)
            {
                try { con.close(); } catch (SQLException ignore) {}
            }
        }
        return fgiPartsAndCustomers;
    }


    /**
     *
     * @param colValue
     * @param processStepId
     * @param col 1 -> part number, 2 -> mktg part number
     * @return
     * @throws ScalarException
     */
    public static boolean checkForDuplicateValueInProductTable( String colValue, int processStepId, int col ) throws ScalarException
    {
        Connection con = null;
        PreparedStatement pStmt = null;
        ResultSet rs = null;
        String colName = "";
        if (col == 1) {
            colName = "PART_NUMBER";
        } else if (col == 2) {
            colName = "P_MARKETING_PART_NUMBER";
        }

        boolean present = false;
        try
        {
            con = DataSource.getConnection();
            if( processStepId != -1)
            {
                pStmt = con.prepareStatement("SELECT PRODUCT_ID FROM PRODUCT WHERE "+colName+" = ? AND PROCESSSTEP_ID <> ?");
                pStmt.setString(1, colValue);
                pStmt.setInt(2, processStepId);
            }
            else
            {
                pStmt = con.prepareStatement("SELECT PRODUCT_ID FROM PRODUCT WHERE "+colName+" = ?");
                pStmt.setString(1, colValue);
            }
            rs = pStmt.executeQuery();
            if( rs.next() )
            {
                present = true;
            }
        }
        catch( Exception e)
        {
            e.printStackTrace();
            throw new ScalarException( "Couldn't check for cuplicate Part Numbers in database");
        }
        finally
        {
            if (rs != null)
            {
                try { rs.close(); } catch (SQLException ignore) {}
            }
            if (pStmt != null)
            {
                try { pStmt.close(); } catch (SQLException ignore) {}
            }
            if (con != null)
            {
                try { con.close(); } catch (SQLException ignore) {}
            }
        }
        return present;
    }

    public static void updateProcessStepTable( DBTransaction dbTransaction, int processStepId, String comments) throws ScalarException
    {
        Connection con = null;
        PreparedStatement pStmt = null;
        boolean status = false;
        try
        {
            if( dbTransaction != null)
            {
                con = dbTransaction.getConnection();
            }
            else
            {
                con = DataSource.getConnection();
            }
            pStmt = con.prepareStatement( SqlQueryMappings.get("operations.process.processStep.updateComments"));
            pStmt.setString(1, comments);
            pStmt.setInt(2, processStepId);
            pStmt.executeUpdate();
            status = true;
        }
        catch( Exception e)
        {
            e.printStackTrace();
            throw new ScalarException("Couldn't update Process Step table with comments");
        }
        finally
        {
            if (dbTransaction == null) {
                try {
                    if (con != null) {
                        if (status == true) {
                            con.commit();
                        }
                        else {
                            con.rollback();
                        }
                    }
                }
                catch (SQLException ignore) {}
            }

            if(pStmt != null){
                try{ pStmt.close(); }catch(Exception e){}
            }
            if(con != null){
                try{ con.close(); }catch(Exception e){}
            }
        }
    }

    public static void updateProductTable( DBTransaction dbTransaction, int processStepId, FinishedGoodsEntryForm form) throws ScalarException
    {
        Connection con = null;
        PreparedStatement pStmt = null;
        boolean status = false;
        try
        {
            if( dbTransaction != null)
            {
                con = dbTransaction.getConnection();
            }
            else
            {
                con = DataSource.getConnection();
            }
            pStmt = con.prepareStatement( SqlQueryMappings.get("operations.finishedGoods.saveProductInfo"));
            pStmt.setString(1, form.getFinishedGoodsPartNo());
            pStmt.setString(2, form.getFinishedGoodsPartNo() );
            pStmt.setString(3, form.getDescription() );
            pStmt.setInt(4, form.isApproved()?0:1 );
            pStmt.setString(5, form.getProgramCode() );
            pStmt.setString( 6, form.getAlgorithm());
            pStmt.setString( 7, form.getCrc());
            pStmt.setString( 8, form.getIsBlankProduct() );
            pStmt.setInt( 9, processStepId);
            pStmt.executeUpdate();
            status = true;
        }
        catch( Exception e)
        {
            e.printStackTrace();
            throw new ScalarException("Couldn't update Product table");
        }
        finally
        {
            if (dbTransaction == null) {
                try {
                    if (con != null) {
                        if (status == true) {
                            con.commit();
                        }
                        else {
                            con.rollback();
                        }
                    }
                }
                catch (SQLException ignore) {}
            }

            if(pStmt != null){
                try{ pStmt.close(); }catch(Exception e){}
            }
            if(con != null){
                try{ con.close(); }catch(Exception e){}
            }
        }
    }

    public static ArrayList getFinishedGoodsProductsList( FinishedGoodsSearchForm finishedGoodsSearchForm, DBTransaction dbTransaction ) throws ScalarException
    {
        ArrayList finishedGoodsList = new ArrayList();
		Connection conn 		= null;
		CallableStatement cstmt = null;
		ResultSet rs 			= null;
        FinishedGoodsPartNumber fgPartNumber = null;

		try
		{
            if( dbTransaction == null )
            {
                conn	= DataSource.getConnection();
            }
            else
            {
                conn	= dbTransaction.getConnection();
            }
            cstmt 	= conn.prepareCall( SqlQueryMappings.get("operations.finishedGoods.searchForFinishedGoods"));
            cstmt.setInt( 1, finishedGoodsSearchForm.getBaseProductId() );
            cstmt.setInt( 2, finishedGoodsSearchForm.getFgiProductId() );
            cstmt.setInt( 3, finishedGoodsSearchForm.getCustomerId() );
            cstmt.setString( 4, finishedGoodsSearchForm.getProgramCode() );
			rs 		= cstmt.executeQuery();


			while ( rs.next() )
			{
                fgPartNumber = new FinishedGoodsPartNumber();
                fgPartNumber.setCustomerName(rs.getString("Customer_Name"));
                fgPartNumber.setBasePartNumber( rs.getString("Base_Product_Name"));
                fgPartNumber.setFgProcessStepId( rs.getInt( "ProceessStep_ID" ) );
                fgPartNumber.setPartNumber( rs.getString("Part_Number") );
                fgPartNumber.setProgramCode( rs.getString("Program_Code") );
                fgPartNumber.setFgCustomerId( rs.getInt("Customer_ID") );
                finishedGoodsList.add( fgPartNumber );

			}
		}
		catch( Exception e )
		{
            e.printStackTrace();
			throw new ScalarException ("Error while retreiving the Finished Goods  list");
		}
		finally
		{
			if (rs != null)
			{
				try { rs.close(); } catch (SQLException ignore) {}
            }

			if (cstmt != null)
			{
				try { cstmt.close(); } catch (SQLException ignore) {}
            }

			if (conn != null)
			{
				try { conn.close(); } catch (SQLException ignore) {}
			}
		}
        return finishedGoodsList;
    }


    public static int saveProcessStepProduct (DBTransaction dbTransaction, int processStepID,String partNumber, int baseProductID, String comments, int isApproved, String productType, int customerId, String programCode, String algorithm, String crc, String isBlankProduct ) throws ScalarException
        {
            Connection conn = null;
            PreparedStatement pstmt = null;
            ResultSet rset = null;
            boolean status = false;
            int productId = -1;

            try
            {
                //Get the connection from regular datasource if its not in a manual transaction
                if (dbTransaction == null)
                {
                    conn = DataSource.getConnection();
                }
                else
                {
                    conn = dbTransaction.getConnection();
                }

                pstmt = conn.prepareStatement (SqlQueryMappings.get("operations.finishedGoods.insertProduct"));

                pstmt.setInt ( 1, processStepID );
                pstmt.setString ( 2, partNumber );
                pstmt.setInt ( 3, baseProductID );
                pstmt.setString ( 4, partNumber );
                pstmt.setString ( 5, comments );
                pstmt.setInt (6, isApproved );
                pstmt.setString (7, productType );
                pstmt.setInt( 8, customerId);
                pstmt.setString( 9, programCode);
                pstmt.setString( 10, algorithm);
                pstmt.setString( 11, crc);
                pstmt.setInt(12, 1);
                pstmt.setString ( 13, isBlankProduct );
                pstmt.executeUpdate();

                pstmt.close();
                pstmt = null;

                pstmt = conn.prepareStatement ("SELECT @@IDENTITY as product_id");
                rset = pstmt.executeQuery();

                if (rset.next() != false)
                {
                    productId =  rset.getInt ("product_id");
                }

                rset.close();
                rset = null;
                status = true;

            }

            catch(Exception sql)
            {
                sql.printStackTrace();
                 throw new ScalarException("Could not store product information", sql);
            }

            finally
            {
                // Commit/Rollback here it selef -- if its not in a manual transaction
                if (dbTransaction == null) {
                    try
                    {
                        if (conn != null)
                        {
                            if (status == true)
                            {
                                conn.commit();
                            }
                            else
                            {
                                conn.rollback();
                            }
                        }
                    }
                    catch (SQLException ignore) {}
                }

                if(pstmt != null)
                {
                    try{pstmt.close();}catch(SQLException se){}
                }
                if(conn != null)
                {
                    try{ conn.close(); }catch(SQLException se){}
                }
            }
            return productId;
        }

    public static ArrayList loadAlgorithmList () throws ScalarException
    {

        ArrayList algorithmList 	= new ArrayList();

        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rs            = null;

        try
        {
            conn  	= DataSource.getConnection( Constants.DATASOURCE_TYPE_LOCAL );
            pstmt 	= conn.prepareStatement( SqlQueryMappings.get("operations.finishedGoods.getAlgorithmList") );
            rs 		= pstmt.executeQuery();

            while( rs.next() )
            {
                String algorithmId = rs.getString( "Algorithm_Id");
                String algorithmName = rs.getString("Algorithm_Name");
                if( algorithmName != null)
                {
                    algorithmName = algorithmName.trim();
                    algorithmList.add ( new LabelValueBean( algorithmName, algorithmName) );
                }
            }
        }
        catch( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException( "Could not Load Algorithm list ", e );
        }
        finally
        {
            if ( rs != null )
            {
                try
                {
                    rs.close();
                }
                catch ( Throwable th )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close();
                }
                catch ( Throwable th )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close();
                }
                catch ( Throwable th )
                {
                }
            }
        }
        return algorithmList;
    }

    public static int getAlgorithmId( String algorithmName) throws ScalarException
    {

        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rs            = null;
        int algorithmId = -1;
        try
        {
            conn  	= DataSource.getConnection( Constants.DATASOURCE_TYPE_LOCAL );
            pstmt 	= conn.prepareStatement( SqlQueryMappings.get("operations.finishedGoods.getAlgorithmId") );
            pstmt.setString(1, algorithmName);
            rs 		= pstmt.executeQuery();
            if( rs.next() )
            {
                algorithmId = rs.getInt( "algorithm_id");
            }
        }
        catch( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException( "Could not get Algorithm Id for Algorithm Name : " + algorithmName, e );
        }
        finally
        {
            if ( rs != null )
            {
                try
                {
                    rs.close();
                }
                catch ( Throwable th )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close();
                }
                catch ( Throwable th )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close();
                }
                catch ( Throwable th )
                {
                }
            }
        }
        return algorithmId;
    }


    public static int saveAlgorithmName( DBTransaction dbTransaction, String algorithmName ) throws ScalarException
    {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        int algorithmId = -1;
        boolean success = false;
        try
        {
            // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement (SqlQueryMappings.get("operations.finishedGoods.saveAlgorithm"));
            pstmt.setString (1, algorithmName);
            pstmt.executeUpdate();

            pstmt.close();
            pstmt = null;

            pstmt = conn.prepareStatement ("SELECT @@IDENTITY as algorithm_id");
            rs = pstmt.executeQuery();
            if (rs.next() != false)
            {
                algorithmId =  rs.getInt ("algorithm_id");
            }
            success = true;
        }

        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException ("Error Saving Algorithm Name", sqle);
        }

        finally
        {
        // Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbTransaction == null)
            {
                try
                {
                    if (conn != null)
                    {
                        if (success == true)
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore) {}
            }
            if(rs != null)
            {
                try
                {
                    rs.close();
                }
                catch(SQLException ignore){}
            }
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore){}
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore){}
            }
        }
        return algorithmId;
    }
    /**
     *
     * @param dbTransaction
     * @param processStepId
     * @param customerIds
     * @throws ScalarException
     */
    public static void saveCustomersForFG( DBTransaction dbTransaction, int processStepId, String[] customerIds ) throws ScalarException
    {
        Connection        conn  = null;
        PreparedStatement pstmt = null;

        boolean status = false;
        int customerId = -1;

        try
        {
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement( SqlQueryMappings.get( "operations.finishedGoods.insertCustomersForFG") );
            for( int i = 0; i < customerIds.length; i++ )
            {
                customerId = -1;
                try
                {
                    customerId = Integer.parseInt( customerIds[i] );
                }
                catch( NumberFormatException e)
                {
                    continue;
                }
                pstmt.setInt( 1, processStepId );
                pstmt.setInt( 2, customerId );
                pstmt.addBatch();
            }
            pstmt.executeBatch();
        }
        catch(SQLException sql)
        {
            sql.printStackTrace();
            throw new ScalarException(" Save saveProductMap Failed", sql);
        }
        finally
        {
            if (dbTransaction == null) {
                try {
                    if (conn != null) {
                        if ( status == true ) {
                            conn.commit();
                        }
                        else {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore) {}
            }

            if(pstmt != null){
                try{ pstmt.close(); }catch(Exception e){}
            }
            if(conn != null){
                try{ conn.close(); }catch(Exception e){}
            }
        }
    }
    /**
     *
     * @param processStepId
     * @return
     * @throws ScalarException
     */
    public static String[] loadCustomersForFG( int processStepId ) throws ScalarException
    {
        Connection conn 		= null;
        PreparedStatement pstmt = null;
        ResultSet rs 			= null;
        String[] customerIds = null;
        try
        {
            conn  = DataSource.getConnection();
            pstmt = conn.prepareStatement( SqlQueryMappings.get( "operations.finishedGoods.getCustomersForFG" ) );
            pstmt.setInt( 1, processStepId );
            rs = pstmt.executeQuery();
            ArrayList customersList = new ArrayList();
            while( rs.next() )
            {
                customersList.add( rs.getString( "CUSTOMER_ID" ) );
            }
            customerIds = new String[ customersList.size()];
            customersList.toArray( customerIds );
        }
        catch( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException ("Could not get the product ids for FGI products for process step id : " + processStepId );
        }
        finally
        {
            if (rs != null)
            {
                try { rs.close(); } catch (SQLException ignore) {}
            }

            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }

            if (conn != null)
            {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }
        return customerIds;
    }
    /**
     *
     * @param dbTransaction
     * @param processStepId
     * @throws ScalarException
     */
    public static void deleteCustomersForFG( DBTransaction dbTransaction, int processStepId ) throws ScalarException
    {
        Connection        con   = null;
        PreparedStatement pstmt = null;

        boolean status = false;
        try
        {
            if( dbTransaction != null)
            {
                con = dbTransaction.getConnection();
            }
            else
            {
                con = DataSource.getConnection();
            }
            pstmt = con.prepareStatement( SqlQueryMappings.get( "operations.finishedGoods.deleteCustomersForFG" ) );
            pstmt.setInt(1, processStepId);
            pstmt.executeUpdate();
            status = true;
        }
        catch( Exception e)
        {
            e.printStackTrace();
            throw new ScalarException("Couldn't update Customer FG Mapping table");
        }
        finally
        {
            if (dbTransaction == null) {
                try {
                    if (con != null) {
                        if (status == true) {
                            con.commit();
                        }
                        else {
                            con.rollback();
                        }
                    }
                }
                catch (SQLException ignore) {}
            }

            if(pstmt != null){
                try{ pstmt.close(); }catch(Exception e){}
            }
            if(con != null){
                try{ con.close(); }catch(Exception e){}
            }
        }
    }

}
